{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "intro",
   "metadata": {},
   "source": [
    "# Pandas基础\n",
    "\n",
    "## 什么是Pandas？\n",
    "\n",
    "Pandas是Python数据分析的核心库，用于处理结构化数据（表格数据）。\n",
    "\n",
    "**核心优势**：\n",
    "- 处理大规模数据比Excel更快\n",
    "- 支持多种文件格式（CSV、Excel、JSON、SQL等）\n",
    "- 强大的数据清洗和转换功能\n",
    "- 与NumPy、Matplotlib无缝集成\n",
    "\n",
    "---\n",
    "\n",
    "## 核心数据结构\n",
    "\n",
    "### Series：一维数据\n",
    "带标签的一维数组\n",
    "\n",
    "### DataFrame：二维数据  \n",
    "带标签的二维表格（类似Excel表格）"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "series",
   "metadata": {},
   "source": [
    "## 1. Series基础"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "series-demo",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建Series\n",
    "sales = pd.Series([2380, 3150, 2980, 4280, 3520], \n",
    "                  index=['周一', '周二', '周三', '周四', '周五'],\n",
    "                  name='日销售额')\n",
    "print(\"销售数据：\")\n",
    "print(sales)\n",
    "\n",
    "# 访问元素\n",
    "print(f\"\\n周一销售额: {sales['周一']}\")\n",
    "print(f\"第2天销售额: {sales.iloc[1]}\")\n",
    "\n",
    "# 常用统计\n",
    "print(f\"\\n平均销售额: {sales.mean():.2f}\")\n",
    "print(f\"总销售额: {sales.sum():.2f}\")\n",
    "print(f\"最高销售额: {sales.max()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dataframe",
   "metadata": {},
   "source": [
    "## 2. DataFrame创建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df-create",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方式1：从字典创建\n",
    "data = {\n",
    "    '姓名': ['张三', '李四', '王五', '赵六'],\n",
    "    '年龄': [25, 28, 32, 35],\n",
    "    '部门': ['销售', '技术', '技术', '市场'],\n",
    "    '工资': [8000, 12000, 15000, 10000]\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "print(\"员工信息：\")\n",
    "print(df)\n",
    "\n",
    "# 查看数据\n",
    "print(\"\\n数据信息：\")\n",
    "print(df.info())\n",
    "\n",
    "print(\"\\n统计信息：\")\n",
    "print(df.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "read-files",
   "metadata": {},
   "source": [
    "## 3. 读取数据文件\n",
    "\n",
    "### 读取CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "read-csv",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用实际存在的数据文件\n",
    "df_sales = pd.read_csv('../data/sales_data.csv')\n",
    "print(\"销售数据预览：\")\n",
    "print(df_sales.head())\n",
    "\n",
    "# 常用参数示例\n",
    "df_sales2 = pd.read_csv('../data/sales_data.csv',\n",
    "                        usecols=[0, 1, 2],  # 只读取前3列\n",
    "                        nrows=5)             # 只读取前5行\n",
    "print(\"\\n筛选读取：\")\n",
    "print(df_sales2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "read-excel",
   "metadata": {},
   "source": [
    "### 读取Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "read-excel-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取Excel文件\n",
    "df_emp = pd.read_excel('../data/company_data.xlsx', sheet_name='员工信息')\n",
    "print(\"员工信息：\")\n",
    "print(df_emp.head())\n",
    "\n",
    "# 读取所有sheet\n",
    "all_sheets = pd.read_excel('../data/company_data.xlsx', sheet_name=None)\n",
    "print(f\"\\nSheet列表: {list(all_sheets.keys())}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "read-json",
   "metadata": {},
   "source": [
    "### 读取JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "read-json-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "# 读取JSON\n",
    "with open('../data/products.json', 'r', encoding='utf-8') as f:\n",
    "    data = json.load(f)\n",
    "\n",
    "# 转换为DataFrame\n",
    "df_products = pd.json_normalize(data['products'])\n",
    "print(\"产品信息：\")\n",
    "print(df_products)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "read-sql",
   "metadata": {},
   "source": [
    "### 读取数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "read-sql-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# 连接数据库\n",
    "conn = sqlite3.connect('../data/company.db')\n",
    "\n",
    "# 读取表\n",
    "df_db = pd.read_sql_query(\"SELECT * FROM employees\", conn)\n",
    "print(\"数据库查询结果：\")\n",
    "print(df_db)\n",
    "\n",
    "# 复杂查询\n",
    "query = \"\"\"\n",
    "SELECT name, salary, department\n",
    "FROM employees\n",
    "WHERE salary > 10000\n",
    "\"\"\"\n",
    "df_high = pd.read_sql_query(query, conn)\n",
    "print(\"\\n高薪员工：\")\n",
    "print(df_high)\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "data-access",
   "metadata": {},
   "source": [
    "## 4. 数据访问\n",
    "\n",
    "### 选择列和行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "select",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用前面创建的员工数据\n",
    "data = {\n",
    "    '姓名': ['张三', '李四', '王五', '赵六'],\n",
    "    '年龄': [25, 28, 32, 35],\n",
    "    '部门': ['销售', '技术', '技术', '市场'],\n",
    "    '工资': [8000, 12000, 15000, 10000]\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 选择列\n",
    "print(\"选择单列（返回Series）：\")\n",
    "print(df['姓名'])\n",
    "\n",
    "print(\"\\n选择多列（返回DataFrame）：\")\n",
    "print(df[['姓名', '工资']])\n",
    "\n",
    "# 选择行\n",
    "print(\"\\n使用iloc选择行：\")\n",
    "print(df.iloc[0])      # 第一行\n",
    "\n",
    "print(\"\\n使用loc选择：\")\n",
    "print(df.loc[0:2, ['姓名', '工资']])  # 前3行的姓名和工资"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "filter",
   "metadata": {},
   "source": [
    "### 条件筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "filter-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单条件\n",
    "print(\"工资大于10000的员工：\")\n",
    "print(df[df['工资'] > 10000])\n",
    "\n",
    "# 多条件\n",
    "print(\"\\n技术部门且工资大于10000：\")\n",
    "print(df[(df['工资'] > 10000) & (df['部门'] == '技术')])\n",
    "\n",
    "# isin筛选\n",
    "print(\"\\n技术或销售部门：\")\n",
    "print(df[df['部门'].isin(['技术', '销售'])])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "operations",
   "metadata": {},
   "source": [
    "## 5. 数据操作\n",
    "\n",
    "### 添加/删除/修改"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "modify",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加列\n",
    "df['年终奖'] = df['工资'] * 2\n",
    "print(\"添加年终奖列：\")\n",
    "print(df)\n",
    "\n",
    "# 修改数据\n",
    "df.loc[0, '工资'] = 9000\n",
    "print(\"\\n修改后：\")\n",
    "print(df)\n",
    "\n",
    "# 删除列\n",
    "df = df.drop('年终奖', axis=1)\n",
    "print(\"\\n删除年终奖列：\")\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stats",
   "metadata": {},
   "source": [
    "### 统计分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "stats-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基础统计\n",
    "print(\"工资统计：\")\n",
    "print(f\"平均工资: {df['工资'].mean()}\")\n",
    "print(f\"最高工资: {df['工资'].max()}\")\n",
    "print(f\"最低工资: {df['工资'].min()}\")\n",
    "\n",
    "# 分组统计\n",
    "print(\"\\n按部门统计工资：\")\n",
    "dept_stats = df.groupby('部门')['工资'].agg(['mean', 'sum', 'count'])\n",
    "print(dept_stats)\n",
    "\n",
    "# 值计数\n",
    "print(\"\\n部门人数分布：\")\n",
    "print(df['部门'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "export",
   "metadata": {},
   "source": [
    "## 6. 导出数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "export-code",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出CSV\n",
    "df.to_csv('output.csv', index=False, encoding='utf-8')\n",
    "\n",
    "# 导出Excel\n",
    "df.to_excel('output.xlsx', index=False, sheet_name='员工数据')\n",
    "\n",
    "# 导出JSON\n",
    "df.to_json('output.json', orient='records', force_ascii=False)\n",
    "\n",
    "print(\"数据已导出！\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cheatsheet",
   "metadata": {},
   "source": [
    "## 速查表\n",
    "\n",
    "### 常用方法\n",
    "\n",
    "| 功能 | 方法 | 示例 |\n",
    "|------|------|------|\n",
    "| 查看前N行 | `head(n)` | `df.head(5)` |\n",
    "| 查看后N行 | `tail(n)` | `df.tail(5)` |\n",
    "| 数据信息 | `info()` | `df.info()` |\n",
    "| 统计描述 | `describe()` | `df.describe()` |\n",
    "| 数据形状 | `shape` | `df.shape` |\n",
    "| 列名列表 | `columns` | `df.columns` |\n",
    "| 数据类型 | `dtypes` | `df.dtypes` |\n",
    "| 缺失值检查 | `isnull()` | `df.isnull().sum()` |\n",
    "| 去重 | `drop_duplicates()` | `df.drop_duplicates()` |\n",
    "| 排序 | `sort_values()` | `df.sort_values('工资')` |\n",
    "\n",
    "### 数据选择\n",
    "\n",
    "| 操作 | 语法 |\n",
    "|------|------|\n",
    "| 选择列 | `df['col']` 或 `df[['col1', 'col2']]` |\n",
    "| 位置索引 | `df.iloc[行, 列]` |\n",
    "| 标签索引 | `df.loc[行, 列]` |\n",
    "| 条件筛选 | `df[df['col'] > value]` |\n",
    "\n",
    "### 文件读写\n",
    "\n",
    "| 格式 | 读取 | 写入 |\n",
    "|------|------|------|\n",
    "| CSV | `read_csv()` | `to_csv()` |\n",
    "| Excel | `read_excel()` | `to_excel()` |\n",
    "| JSON | `read_json()` | `to_json()` |\n",
    "| SQL | `read_sql()` | `to_sql()` |\n",
    "\n",
    "---\n",
    "\n",
    "## 小结\n",
    "\n",
    "**核心概念**：\n",
    "- **Series**：一维数据（带索引）\n",
    "- **DataFrame**：二维表格数据\n",
    "\n",
    "**核心操作**：\n",
    "1. **创建**：字典、列表、文件\n",
    "2. **读取**：CSV、Excel、JSON、SQL\n",
    "3. **查看**：`head()`, `info()`, `describe()`\n",
    "4. **选择**：`[]`, `loc[]`, `iloc[]`\n",
    "5. **筛选**：布尔条件\n",
    "6. **统计**：`mean()`, `sum()`, `groupby()`\n",
    "7. **导出**：`to_csv()`, `to_excel()`"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
